package LawsonTransformJava.pgm;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.*;
import java.lang.reflect.InvocationTargetException;

import LawsonTransformJava.xref.CrossRef;
import LawsonTransformJava.xref.XrefPersAction;
import LawsonTransformJava.xref.XrefPosition;
import LawsonTransformJava.dao.DAOFactoryAccessDB;
import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAOHRHistory;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblHRHistory;

public class HR521 extends DAObject implements 
DAOHRHistory, Runnable {

	private TblHRHistory hrh = new TblHRHistory();

	private XrefPersAction xPersActions = null;
	private int dateStampYear = 0;

	public HR521(File output, File errOutput, File errReport, int dateStampYear) throws SQLException {
		try {
			this.output = new FileWriter(this.outputFile = output);
			this.errOutput = new FileWriter(this.errOutputFile = errOutput);
			this.errReport = new Report(errReport, new Date());
			this.dateStampYear = dateStampYear;

			//initialization
			init();
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

	private void init() throws IOException, SQLException {
		// Set the data variables
		data = DAOFactoryOracleDB.getDAOOracleDB(true);
		xPersActions = new XrefPersAction();

		errReport.setPgmName(this.getClass().getSimpleName());

		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
		stmtData = data.getDataStatement();	    
		countStmtData = data.getCountStatement();
		//Set the Date Format for output
		df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
	}

	public TblHRHistory findHRHistory() {
		try {
			String schemax=data.getSchema();	
			if (whereClause==null || whereClause.equals("")) {
				whereClause = " WHERE to_char(" + TABLE_NAME + ".DATE_STAMP, 'yyyy') = " + dateStampYear;
			} else {
				whereClause = whereClause + " AND (to_char(" + TABLE_NAME + ".DATE_STAMP, 'yyyy') = " + dateStampYear;
			}
			sqlData = "SELECT "      + 
					schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
					schemax + "." + TABLE_NAME  + ".BEG_DATE, " +
					schemax + "." + TABLE_NAME  + ".FLD_NBR, " +
					schemax + "." + TABLE_NAME  + ".A_VALUE, " +
					schemax + "." + TABLE_NAME  + ".D_VALUE, " +
					schemax + "." + TABLE_NAME  + ".N_VALUE, " +
					schemax + "." + TABLE_NAME  + ".USER_ID, " + 
					schemax + "." + TABLE_NAME  + ".POS_LEVEL, " +
					schemax + "." + TABLE_NAME  + ".CURRENCY_CODE, " +
					schemax + "." + TABLE_NAME2 + ".ACTION_CODE, " + 	
					schemax + "." + TABLE_NAME2 + ".REASON_01, " + 
					schemax + "." + TABLE_NAME2 + ".REASON_02, " + 
					schemax + "." + TABLE_NAME2 + ".ANT_END_DATE " + 
					"FROM "       + schemax + "." + TABLE_NAME  +
					" LEFT JOIN " + schemax + "." + TABLE_NAME2 +
					" ON "        + schemax + "." + TABLE_NAME  + ".ACT_OBJ_ID" +
					"="		 + schemax + "." + TABLE_NAME2 + ".OBJ_ID " 
					+ (whereClause==null?"":whereClause);

			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			hrh = (TblHRHistory) createObject(rslt, new TblHRHistory());
			return hrh;			
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public TblHRHistory findHRHistory(String employeeNumber) {
		try {
			String schemax=data.getSchema();
			if (whereClause==null || whereClause.equals("")) {
				whereClause = "WHERE " + TABLE_NAME + ".EMPLOYEE=" + employeeNumber +
					" AND (to_char(" + TABLE_NAME + ".DATE_STAMP, 'yyyy') = " + dateStampYear;
			} else {
				whereClause = whereClause + " AND " + TABLE_NAME + ".EMPLOYEE=" + employeeNumber +
					" AND (to_char(" + TABLE_NAME + ".DATE_STAMP, 'yyyy') = " + dateStampYear;
			}
			sqlData = "SELECT "      + 
					schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
					schemax + "." + TABLE_NAME  + ".BEG_DATE, " +
					schemax + "." + TABLE_NAME  + ".FLD_NBR, " +
					schemax + "." + TABLE_NAME  + ".A_VALUE, " +
					schemax + "." + TABLE_NAME  + ".D_VALUE, " +
					schemax + "." + TABLE_NAME  + ".N_VALUE, " +
					schemax + "." + TABLE_NAME  + ".USER_ID, " + 
					schemax + "." + TABLE_NAME  + ".POS_LEVEL, " +
					schemax + "." + TABLE_NAME  + ".CURRENCY_CODE, " +
					schemax + "." + TABLE_NAME2 + ".ACTION_CODE, " + 	
					schemax + "." + TABLE_NAME2 + ".REASON_01, " + 
					schemax + "." + TABLE_NAME2 + ".REASON_02, " + 
					schemax + "." + TABLE_NAME2 + ".ANT_END_DATE " + 
					"FROM "       + schemax + "." + TABLE_NAME  +
					" LEFT JOIN " + schemax + "." + TABLE_NAME2 +
					" ON "        + schemax + "." + TABLE_NAME  + ".ACT_OBJ_ID" +
					"="		 + schemax + "." + TABLE_NAME2 + ".OBJ_ID " +
			        " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			hrh = (TblHRHistory) createObject(rslt, new TblHRHistory());
			return hrh;
			//createFields(pep);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return new TblHRHistory();
	}

	public TblHRHistory nextHRHistory() {

		try {
			if (rslt.next()) {
				hasMoreRecs = true;
				hrh = (TblHRHistory) createObject(rslt, new TblHRHistory());
			} else
				hasMoreRecs = false;
			return hrh;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public boolean hasMore () {
		return hasMoreRecs;
	}

	public int countHRHistory() {
		try {
			countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) " +
					"FROM " + data.getSchema() + "." + TABLE_NAME + " " + 
					(whereClause==null?"":whereClause));
			countRecs.next();
			return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
	}

	public void setWhereClause(String where) {
		this.whereClause = where;
	}

	public void run() {
		try {
			//Update current date/time
			System.out.println(errReport.getName() + " Start " + Report.getDateFormat().format(new Date()));

			hrh = findHRHistory();
//			System.out.println(whereClause + " " + countHRHistory());
			while (hasMore()) {
				writeHR521Layout(hrh);
				hrh = nextHRHistory();
			}
			//Update current date/time
			System.out.println(errReport.getName() + " Finish " + Report.getDateFormat().format(new Date()));

			//Write Record Counts
			errReport.writeRecordCounts(this.countHRHistory());
			
			//Reclaim Resources
			fileCleanup();

		} catch (IOException e) {
			// Could not write properties file
			// Could not create output file
			System.out.println("IOException: " + e);
		}

	}

	//Output the HR521 format
	public void writeHR521Layout(TblHRHistory hrh) throws IOException {

		String writeValue = null;

		writeValue =
				"1" + "," +
				hrh.getEmployee()  + "," +
				hrh.getBeg_date() + "," +
				xPersActions.xlate(hrh.getAction_code()) + "," +
				hrh.getFld_nbr() + "," +
				hrh.getA_value() + "," +
				hrh.getD_value() + "," +
				hrh.getN_value() + "," +
				hrh.getUser_id() + "," +
				hrh.getReason_01() + "," +
				hrh.getReason_02() + "," +
				hrh.getAnt_end_date() + "," +
				hrh.getPos_level() + "," +
				// @ TODO need to make sure what will be loaded into the salary class.
				"" + "," + 		// Salary Class?
				hrh.getCurrency_code()
			;
		
		if (CrossRef.isError()) {
			//Increment Record in Error
			errReport.addErrorCnt();
			//Write to Error File
			this.errOutput.write(writeValue + "\n");
			this.errOutput.flush();
			CrossRef.resetError();
		}
		else {
			//Increment Record Written
			errReport.addWrittenCnt();
			//Write to Load File
			this.output.write(writeValue + "\n");
			this.output.flush();
		}
	}

	public void fileCleanup() throws IOException {
		errReport.flush();
		errReport.close();
		output.flush();
		output.close();
		errOutput.flush();
		errOutput.close();
	}
}